IF OBJECT_ID('ssf.ddl_get_object_definition') IS NOT NULL
	DROP PROCEDURE ssf.ddl_get_object_definition
GO

CREATE PROCEDURE ssf.ddl_get_object_definition
	@object_name NVARCHAR(512)
	, @object_definition NVARCHAR(MAX) = NULL OUTPUT
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @row_count BIGINT
			, @sql VARCHAR(8000) -- or (MAX)
			, @nsql NVARCHAR(4000) -- or (MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@db_name NVARCHAR(128) = QUOTENAME(PARSENAME(@object_name, 3))
-- ============

-- ============
/*<code_comment><![CDATA[initialize output parameter]]></code_comment>*/
SET @object_definition = NULL
-- ============

-- ============
/*<code_comment><![CDATA[exit if null or blank object name]]></code_comment>*/
IF NULLIF(@object_name, '') IS NULL
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[get object definition]]></code_comment>*/
SET @nsql = CASE WHEN NULLIF(@db_name, '') IS NOT NULL
	THEN 'USE ' + @db_name + @crlf
	ELSE ''
	END
	+ 'SET @object_definition = OBJECT_DEFINITION(OBJECT_ID(' + @quote + @object_name + @quote + '))'

EXECUTE sp_executesql @nsql
	, N'@object_definition NVARCHAR(MAX) OUTPUT'
	, @object_definition OUTPUT

SET @sql_error = @@ERROR

IF @sql_error <> 0
BEGIN
	RAISERROR('Failed to get the object definition for <%s>.', 16, 1, @object_name)
	RETURN @sql_error
END
-- ============

RETURN @@ERROR
GO
